KS_RMAS

1

SO Number

string

30

Y

A lookup of order_header.order_no is found based on value

2

Kommerce Server ID

integer

4

This is only necessary when an order was originated in KS, imported into ERP system. The ERP system is required to track the SQL PK value and send it back to KS in this export along the actual ERP order number reference above.

3

Customer Number

string

30

Y

A lookup of business_entity.business_no is found based on value and assumes the business_entity.business_type = 'C'

4

Order Date

datetime

5

Contact No

string

30

Lookup contact.contact_no based on value=contact.contact_xref_id

6

Purchase Order

string

50

7

Status

string

1

Y

'O' = Open
'C' = Closed
'X' =Cancelled

8

SO Item Number

integer

4

Y

9

Part Number

string

25

Y

If the item_id does not exist in item_master table, the order_item is still updated but the value is stored in order_item.item_xref_id column.

10

SO Item Desc

string

100

11

Unit Price

money

12

Schedule Date

datetime

Y

This is used as part of the PK so it must be unique to a sales order line

13

Schedule Qty

integer

Y

14

Ship To Number

** This is no longer supported as the shipping address can be unique to an order and therefore render any reporting by shipping location useless. Send blank for this column as a place holder.

15

Ship To Name

string

100

16

Address Line 1

string

100

17

Address Line 2

string

100

18

Address Line 3

string

100

19

City

string

50

20

State

string

50

21

Zip

string

20

22

Country

string

100

23

Ship Via

string

50

There is a ship_via table in the database but no FK relationship, however it may make sense to send up the related code (char(15)) anyway instead of a text string.

24

Freight Amount

money

25

Sales Tax
Amount

money

26

Tax Code

string

15

27

SalesRep

string

10

28

Order Type

string

1

Y

'R' for RMA

29

Line Type

string

1

Y

'S' = shipped line (replaced)
'R' = received line (returned)

Because KS stores Sales Orders, Credits (reverse sales orders), and RMAs in one table set, type fields are used to determine inbound and outbound transactions.

30

Currency Code

string

10

If left blank, the system assumes the order is in the website functional currency (web setting)

31

Trans Freight Amount

money

If the order was transacted in a foreign currency, this amount reflects the foreign currency freight amount otherwise it is the same as the Freight Amount

32

Trans Tax Amount

money

If the order was transacted in a foreign currency, this amount reflects the foreign currency tax amount otherwise it is the same as the Tax Amount

33

Trans Unit Price

money

If the order was transacted in foreign currency, this amount reflects the foreign unit price for the item, otherwise it is the same as the unit price

34

Line Status

string

1

'O' = Open
'C' =Complete
'X' =Cancelled

35

Requested Date

datetime

Customer's request delivery date

36

Service Log

string

30

A lookup of project_task.task_no is done based on value = project_task.task_xref_id. This is used by the webServiceCenter application.

37

KS Service ID

integer

4

Used if the service project was originated in KS and the Service Log above may not be known by KS yet. This field is only used for a lookup (no update).

38

RMA Type

string

30

RMA Type code for the RMA. Must match a code sent in the KS_RMA_TYPES API.

39

RMA Fail Date

datetime

The date of the failure causing the return.

40

Failure Code

string

30

The code that identifies reason for the failure or return

41

Price Code

string

1

'E' Exception
Price
'U' Unit Price

This determines whether the normal pricing occurred (U) or if was overridden with exception pricing (E)

42

Attention

string

50

43

Attention Phone

string

25

44

Hold Code

string

1

Indicates order is on hold. Currently no functionality tied to this field in KS

45

Freight Bill
Type Code

string

2

NOT SUPPORTED FOR RMAS

46

Freight Bill
Account No

string

50

NOT SUPPORTED FOR RMAS

47

Region

string

30

Xref Lookup to find the right row. This is primarily used for webSalesForce application

48

Special Charge Code

integer

Y

Lookup based on special_charge.charge_xref_id

49

Special Charge Order Line

integer

If this field is left blank, then the special charge is assumed to apply to the whole RMA.

50

Special Charge Amount

money

Y

51

Price UOM

string

10

Y

The price unit of measure for this RMA item.

52

Problem

string

75

For the returned item, specify the reported problem

53

Failure Analysis

string

1

Y

'Y' or 'N'

Indicates whether the customer wants failure analysis performed for this item

54

Corrective Action

string

1

Y

'Y' or 'N'

Indicates whether the customer wants corrective action report for this item

55

Service Rep ID

string

10

Service tech ID for the person responsible for diagnosis and repair of the RMA item.

56

Payment Required?

string

1

Y

'Y' or 'N'

This flag can be used to indicate that a payment is required before the repaired RMA line item can be shipped back. Optionally an email can be sent to the customer to remind them to make a payment.

57

Serial Number

string

30

If your system requires one serial number (optional) per line item on the RMA, you may send it in this field. For full audit history of transactions against a serial number, use KS_SERIALHIST API. You must use this API if your ERP system may contain multiple serial numbers per RMA line item.

58

Email Address

string

75

59

Under Warranty

string

1

'Y' or 'N'

A 'Y' indicates there is some level of warranty coverage for the line item

60

Warranty Coverge Amt

money

75

If warranty applies, this is the amount that is covered which may or may not be 100%.